#!/usr/bin/python
#coding=utf-8
from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util
import sys

dtypedict = {'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'CLASS': VARCHAR,
            'SHIFT': VARCHAR,'DATE': VARCHAR,'PRODUCE_START_TIME': VARCHAR,'PRODUCE_END_TIME': VARCHAR,
            'IND': VARCHAR,'IND_NAME': VARCHAR,'IND_UNIT': VARCHAR,
            'INDEX_VALUE': DECIMAL,'DATA_TYPE': VARCHAR,
            'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def initMO0201A(cost_center,unit_code,start_time, end_time, work_time_str):
    query_sql = """select IND,CALC_LOGIC as sql_m,DATA_SOURCE_1 from BGTARAS1.T_ADS_WH_SICB_MO0201A WHERE  UNIT_CODE =  '"""+ unit_code +"""'  ORDER BY SEQ"""
    df = pd.read_sql(query_sql, conn_rds)
    for tup in zip(df['ind'], df['sql_m'], df['data_source_1']):
        sql_m = op.replaceColumn(str(tup[1]), ["P_DATA_TYPE", "P_UNIT", "P_COST_CENTER",  "P_START_TIME", "P_END_TIME", "P_WORK_TIME", "P_WORK_TIME"],
                                       ["0", unit_code,cost_center,start_time,end_time,work_time_str,work_time_str])
        print(sql_m)
        df_fact = util.query(conn=conn_rds if tup[2] == "RDS" else conn_db2, sql=sql_m)
        util.insert(df_fact, conn_rds, "T_ADS_FACT_SICB_MO0201", "BGTARAS1", dtypedict)

def initMO0201C(cost_center,unit_code,start_time, end_time, work_time_str):
    SICB_TMP_MO0201 = """
        CREATE TABLE BGTARAS1.SICB_TMP_MO0201(
            UNIT_CODE VARCHAR(4),
            COST_CENTER VARCHAR(4),
            CLASS VARCHAR(5),
            SHIFT VARCHAR(5),
            PROD_COILNO VARCHAR(20),
            ENTRY_COILNO VARCHAR(20),
            IND VARCHAR(50),
            VALUE DECIMAL(24,6)
        )NOT LOGGED INITIALLY
        IN "BGTARAS1"
    """

    # 读取配置信息
    query_sql = """select IND,COL_PROC_1,COL_PROC_2,DATA_SOURCE_1,DATA_SOURCE_2 from BGTARAS1.T_ADS_WH_SICB_MO0201C WHERE  UNIT_CODE =  '""" + unit_code + """'  ORDER BY REMARK"""
    df = pd.read_sql(query_sql, conn_rds)

    # 读取参与计算的卷信息
    matNo_sql = """ SELECT COST_CENTER,UNIT_CODE,CLASS,SHIFT,PROD_COILNO,ENTRY_COILNO FROM BGTARAS1.T_ADS_FACT_SICB_CD0201 WHERE UNIT_CODE='""" + unit_code + """' AND PRODUCE_TIME BETWEEN '""" + start_time + """' AND '""" + end_time + """'  """
    df_mat = pd.read_sql(matNo_sql, conn_rds)
    if df_mat.__len__()>0:
        try:
            matString = ','.join(map(lambda x: "'" + x + "'", df_mat["prod_coilno"]))

            df_base_info = pd.DataFrame(
                columns=["unit_code", "cost_center", "class", "shift", "prod_coilno", "entry_coilno", "ind", "VALUE"])
            arrayList = []
            for tup in zip(df['ind'], df['col_proc_1'], df['data_source_1']):
                # 查找要用的数据
                sql_1 = op.replaceColumn(str(tup[1]), ["P_MAT_STR", "P_UNIT_CODE", "P_COST_CENTER"],
                                         [matString, unit_code, cost_center])
                print(sql_1)
                df_1 = util.query(conn=conn_rds if tup[2] == "RDS" else conn_db2, sql=sql_1)
                # 与卷信息拼接，并生成临时表数据
                if df_1.__len__()>0:
                    df_tmp_table = op.merge_df(df_mat, df_1,
                                               [["prod_coilno", "unit_code"], ["prod_coilno_2", "unit_code_2"]],
                                               how="left")
                    df_tmp_table = df_tmp_table[
                        ["unit_code", "cost_center", "class", "shift", "prod_coilno", "entry_coilno", "ind", "VALUE"]]
                    df_base_info = df_base_info.append(df_tmp_table)

            # 新建临时表，插入数据
            if df_base_info.__len__() > 0:
                # 建表
                util.createTmpTable(conn_rds, "BGTARAS1", "SICB_TMP_MO0201", SICB_TMP_MO0201)
                dtypedict = op.get_dict_type(df_base_info)
                util.insert(df_base_info, conn_rds, "SICB_TMP_MO0201", "BGTARAS1", dtypedict)

                for tup in zip(df['ind'], df['col_proc_2'], df['data_source_2']):
                    sql_2 = op.replaceColumn(str(tup[1]), ["P_DATA_TYPE", "P_IND", "P_START_TIME", "P_END_TIME", "P_WORK_TIME",
                                                           "P_WORK_TIME"],
                                             ['0', tup[0], start_time, end_time, work_time_str, work_time_str])
                    print(sql_2)
                    df_2 = util.query(conn=conn_rds if tup[2] == "RDS" else conn_db2, sql=sql_2)
                    dtypedict = op.get_dict_type(df_2)
                    util.insert(df_2, conn_rds, "T_ADS_FACT_SICB_MO0201", "BGTARAS1", dtypedict)

                conn_rds.execute("drop table BGTARAS1.SICB_TMP_MO0201")
        except Exception as e:
            print(unit_code+" error:"+e)

def MO0201(cost_center,unit_code,work_time):
    global conn_db2, conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')

    work_time_str = work_time.strftime("%Y%m%tmp_dict%H%M%S")
    if len(work_time_str) == 14:
        if work_time_str[8:10] >= '08' and work_time_str[8:10] < '20':
            start_time = work_time_str[0:8] + "080000"
            end_time = work_time_str[0:8] + "200000"
        elif work_time_str[8:10] >= '20':
            start_time = work_time_str[0:8] + "200000"
            end_time = (work_time + datetime.timedelta(days=1)).strftime("%Y%m%tmp_dict") + "200000"
        elif work_time_str[8:10] < '08':
            start_time = (work_time - datetime.timedelta(days=1)).strftime("%Y%m%tmp_dict") + "200000"
            end_time = work_time_str[0:8] + "080000"
    elif len(work_time_str) == 6:
        start_time = datetime.strptime(work_time_str[0:5] + "01", '%Y%m%tmp_dict')  # 先转成当月
        start_time = (start_time - datetime.timedelta(hours=1)).strftime("%Y%m%tmp_dict") + "200000"  # 前一天晚上20点
        end_time = work_time_str[0:5] + "31200000"  # 当月最后一天
    elif len(work_time_str) == 4:
        start_time = (datetime.strptime(work_time_str[0:3], '%Y') - datetime.timedelta(years=1)).strftime(
            "%Y") + '1231200000'
        end_time = work_time_str[0:3] + '1231200000'

    # 先删除这段时间的数据
    if len(work_time_str) == 14:
        del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_MO0201 WHERE COST_CENTER='""" + cost_center + """'  and UNIT_CODE ='""" + unit_code + """' and DATA_TYPE = '0' and PRODUCE_START_TIME = '""" + str(
            start_time) + """' AND PRODUCE_END_TIME = '""" + str(end_time) + """'"""
    else:
        del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_MO0201 WHERE COST_CENTER='""" + cost_center + """'  and UNIT_CODE ='""" + unit_code + """' and DATA_TYPE = '0' and PRODUCE_START_TIME = '""" + str(
            start_time) + """' AND PRODUCE_END_TIME = '""" + str(
            end_time) + """' and DATE = '""" + work_time_str + """'"""
    conn_rds.execute(del_sql)

    initMO0201A(cost_center, unit_code, start_time, end_time, work_time_str)
    initMO0201C(cost_center, unit_code, start_time, end_time, work_time_str)
    conn_rds.close()
    conn_db2.close()

if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    start = datetime.datetime.now()
    MO0201('MIDD', 'Q418',start)
# Q412，Q502,Q212数据没跑出来，Q403逻辑没搬，Q503逻辑只搬了部分
